import hashlib
import string
import pymysql


# 检查表是否存在，不存在先建表
def checktable_isexist():
    cur.execute("show tables like '%s'" % table_name)
    if not cur.fetchall():
        create_table_sql = '''
        create table if not exists %s
        (
            id int auto_increment,
            uname varchar(30) null,
            pw varchar(100) null,
            pw_md5 varchar(100) null,
            unique(id)
        );''' % table_name
        cur.execute(create_table_sql)


# 检查注册用户是否已存在
def check_exist(username):
    sql = "select * from %s where uname = '%s' " % (table_name, username)
    cur.execute(sql)
    return cur.fetchall()


# 给密码加密加盐
def my_md5(s, salt=""):
    new_s = str(s) + salt
    m = hashlib.md5(new_s.encode())
    return m.hexdigest()


# 检查密码是否符合规则
def check_password(password):
    return set(password) & set(string.ascii_uppercase) and set(password) & \
           set(string.ascii_lowercase) and set(password) & set(string.digits) \
           and len(password) >= 8 and len(password) <= 12


# 检查用户名是否符合规则
def check_username(username):
    return len(username) >= 6 and len(username) <= 12


#  用户名，密码，加密密码落库
def write_user(username, password):
    password_md5 = my_md5(password)
    insert_sql = "insert into %s set uname = '%s',pw= '%s',pw_md5 = '%s'" % (
        table_name, username, password, password_md5)
    cur.execute(insert_sql)


mysql_info = {
    "host": "118.24.3.40",
    "user": "jxz",
    "password": "123456",
    "port": 3306,
    "charset": "utf8",
    "autocommit": True,
    'db': 'jxz'
}

conn = pymysql.connect(**mysql_info)  # 连接数据库，必须加**，因为是多个参数
cur = conn.cursor(pymysql.cursors.DictCursor)     #列表字典展示
table_name = 'users'

for i in range(3):
    username = input("username:").strip()
    password = input("password:").strip()
    cpwd = input("cpwd:").strip()
    if not check_username(username):
        print("用户名必须长度必须6-12")
        continue
    if not check_password(password):
        print("密码必须长度必须8-12，必须包含大小写字母、数字")
        continue
    if password != cpwd:
        print("两次输入密码不一致")
        continue
    checktable_isexist()    #检查表是否存在
    if check_exist(username):    #检查用户名是否存在
        print("用户已经存在")
    else:
        write_user(username, password)
        print("注册成功！")
        break
else:
    print("错误次数过多，最多可以输入3次")

cur.close()
conn.close()